package schoolsys.dao.impl;








import schoolsys.dao.BaseDao;
import schoolsys.dao.StudentDao;
import schoolsys.entity.Grade;
import schoolsys.entity.Student;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * author:wxy
 * create_Date:2021/10/19 11:05
 *
 * tb_name:student
 */
public class StudentDaoImpl extends BaseDao implements StudentDao {
    public List getStuByName(String stuName,int sex){
        List stuList=new ArrayList();
        if(this.getConnection()){
            String sql="select * from student where studentName like ? and sex=?";
            Object[]params={"%"+stuName+"%",sex};
            ResultSet res=this.excuteQuerySql(sql,params);
                try {
                   while(res.next()) {
                       stuList.add(res.getInt("StudentNo"));
                       stuList.add(res.getString("StudentName"));
                       stuList.add(res.getInt("sex"));
                   }
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }finally {
                    //关闭连接
                    closeSourses();
                }
            }
        return stuList;
        }

    @Override
    public Student getStuByNo(String studentNo) {
        if(this.getConnection()){
            String sql="select * from student where StudentNo = ?";
            Object[] params={studentNo};
            ResultSet res=this.excuteQuerySql(sql,params);

            try {
                Student stu;
                while(res.next()) {
                    stu = new Student();
                    stu.setStudentNo(res.getLong("StudentNo"));
                    stu.setLoginPwd(res.getString("LoginPwd"));
                    stu.setStudentName(res.getString("StudentName"));
                    stu.setSex(res.getLong("Sex"));
                    stu.setGradeId(res.getLong("GradeId"));
                    stu.setPhone(res.getString("Phone"));
                    stu.setAddress(res.getString("Address"));
                    stu.setBornDate(res.getDate("BornDate"));
                    stu.setEmail(res.getString("Email"));
                    stu.setIdentityCard(res.getString("IdentityCard"));
                    return stu;
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                //关闭连接
                closeSourses();
            }
        }
        return null;
    }

    @Override
    public List<Student> getStuByNameAll(String studentName ,String studentNo,int currentPage,int pageSize) {
        List<Student> stuList = new ArrayList<>();
        List<Object> paramList=new ArrayList<>();
        if(this.getConnection()){
            StringBuffer sql = new StringBuffer("select s.*,g.gradename from student s left join grade g on s.`GradeId`=g.`GradeId` where 1=1");
            if (studentName!=null&&!"".equals(studentName)){
                sql.append(" and studentName like ? ");
                paramList.add("%"+studentName+"%");
            }
            if (studentNo!=null&&!"".equals(studentNo)){
                sql.append(" and studentNo=? ");
                paramList.add(studentNo);
            }
            sql.append(" limit ?,?");
            paramList.add((currentPage-1)*pageSize);
            paramList.add(pageSize);

            Object[] params = paramList.toArray();
            ResultSet res = this.excuteQuerySql(sql.toString(), params);
            try{
                Student stu;
                Grade grade;
                while(res.next()){
                    stu = new Student();
                    grade = new Grade();
                    stu.setStudentNo(res.getLong("StudentNo"));
                    stu.setLoginPwd(res.getString("LoginPwd"));
                    stu.setStudentName(res.getString("StudentName"));
                    stu.setSex(res.getLong("Sex"));
                    stu.setGradeId(res.getLong("GradeId"));
                    stu.setPhone(res.getString("Phone"));
                    stu.setAddress(res.getString("Address"));
                    stu.setBornDate(res.getDate("BornDate"));
                    stu.setEmail(res.getString("Email"));
                    stu.setIdentityCard(res.getString("IdentityCard"));
                    grade.setGradeId(res.getLong("Gradeid"));
                    grade.setGradeName(res.getString("GradeName"));
                    stu.setGrade(grade);
                    stuList.add(stu);
                    
                }
            }catch (SQLException throwables){
                throwables.printStackTrace();
            }finally {
                //关闭连接
                closeSourses();
            }
        }
        return stuList;
    }

    @Override
    public boolean addStu(Student stu) {
        boolean flag=false;
        if(this.getConnection()){
            String sql="INSERT INTO `myschool`.`student` (`StudentNo`,`StudentName`, `Phone`, `Address`,`email`) " +
                    "VALUES (?,?,?,?,?);";
            Object[]params={stu.getStudentNo(),stu.getStudentName(),stu.getPhone(),stu.getAddress(),stu.getEmail()};
            int  n=this.excuteUpdateSql(sql,params);
            try {
                if(n>0){
                    flag=true;
                }
            } catch (Exception e) {
               e.printStackTrace();
               return false;
            }finally {
                //关闭连接
                closeSourses();
            }
        }
        return flag;
    }

    @Override
    public int deleteStuByNo(long stuNo) {
        int n = 0;
        try {
            if(this.getConnection()){
                String sql="DELETE FROM `myschool`.`student` WHERE `StudentNo` = ?";
                Object[] params={stuNo};
                n=this.excuteUpdateSql(sql,params);
            }
        }catch (Exception e) {
            e.printStackTrace();

        }finally {
            //关闭连接
            closeSourses();
        }
        return n;
    }

    @Override
    public int updateStuByNo(Student stu) {
        int n = 0;
        try {
            if (this.getConnection()){
                String sql="UPDATE `myschool`.`student` SET `LoginPwd` = ?, `StudentName` =?, `GradeId` =?," +
                        "`Phone` =?, `Address` = ?, `BornDate` = ?, `Email` =?, `IdentityCard` = ? WHERE `StudentNo` = ?";
                Object[] params={stu.getLoginPwd(),stu.getStudentName(),stu.getGradeId(),stu.getPhone(),stu.getAddress(),stu.getBornDate(),stu.getEmail(),stu.getIdentityCard(),stu.getStudentNo()};
                n=this.excuteUpdateSql(sql,params);
            }
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭连接
            closeSourses();
        }
        return  n;
    }

    @Override
    public int getTotalCount(String studentName, String studentNo) {
        int totalCount=0;
        List<Object> paramList=new ArrayList<>();
        if(this.getConnection()){
            StringBuffer sql=new StringBuffer("select count(*) from student where 1=1");
            if(studentName!=null && !"".equals(studentName)){
                sql.append(" and studentName like ?");
                paramList.add("%"+studentName+"%");
            }
            if(studentNo!=null && !"".equals(studentNo)){
                sql.append(" and studentNo=? ");
                paramList.add(studentNo);
            }
            Object[]params=paramList.toArray();
            ResultSet res=this.excuteQuerySql(sql.toString(),params);
            try {
                while(res.next()) {
                    totalCount=res.getInt(1);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                closeSourses();
            }
        }
        return totalCount;
    }
}
